import com.mysql.cj.api.jdbc.Statement;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestInsertDataDemo {

    private JdbcTemplate jdbcTemplate;
    private Connection conn = null;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public static InputStream getTestDataInputStream() {
        StringBuilder builder = new StringBuilder();
        for (int i = 1; i <= 10; i++) {
            for (int j = 0; j <= 10000; j++) {

                builder.append(4);
                builder.append("\t");
                builder.append(4 + 1);
                builder.append("\t");
                builder.append(4 + 2);
                builder.append("\t");
                builder.append(4 + 3);
                builder.append("\t");
                builder.append(4 + 4);
                builder.append("\t");
                builder.append(4 + 5);
                builder.append("\n");
            }
        }
        byte[] bytes = builder.toString().getBytes();
        InputStream is = new ByteArrayInputStream(bytes);
        return is;
    }

    /**
     *
     * load bulk data from InputStream to MySQL
     */
    public int bulkLoadFromInputStream(String loadDataSql,
                                       InputStream dataStream) throws SQLException {
        if(dataStream==null){
            System.out.println("InputStream is null ,No data is imported");
            return 0;
        }
        conn = jdbcTemplate.getDataSource().getConnection();
        PreparedStatement statement = conn.prepareStatement(loadDataSql);

        int result = 0;

        if (statement.isWrapperFor(Statement.class)) {

            com.mysql.cj.jdbc.PreparedStatement mysqlStatement = statement
                    .unwrap(com.mysql.cj.jdbc.PreparedStatement.class);

            mysqlStatement.setLocalInfileInputStream(dataStream);
            result = mysqlStatement.executeUpdate();
        }
        return result;
    }

    /**
     * 废弃 文件为GBK,
     * @param args
     */
    public static void main(String[] args) throws Exception {
        /*String testSql = "LOAD DATA INFILE 'utf8_20180118.csv' INTO TABLE aod.tmp_aod_industry_data  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (account,@1,@2,phone_number,@3,@4,@5,send_time,@6,status,send_content) ";
        InputStream dataStream = getTestDataInputStream();
        TestInsertDataDemo dao = new TestInsertDataDemo();
        try {
            long beginTime=System.currentTimeMillis();
            int rows = dao.bulkLoadFromInputStream(testSql, dataStream);
            long endTime=System.currentTimeMillis();
            System.out.println("importing "+rows+" rows data into mysql and cost "+(endTime-beginTime)+" ms!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.exit(1);*/
        long beginTime=System.currentTimeMillis();
        insert();
        long endTime=System.currentTimeMillis();
        System.out.println("importing data into mysql and cost "+(endTime-beginTime)+" ms!");
    }

    /**
     * xiegang
     * @throws Exception
     */
    public static void insert() throws Exception {
        String url = "jdbc:mysql://127.0.0.1:3306/aod?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false";
        String user = "root";
        String password = "CAO1992bing...";
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, user, password);

        String testSql = "LOAD DATA INFILE 'utf8_20180118.csv' INTO TABLE aod.tmp_aod_industry_data  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' (account,@1,@2,phone_number,@3,@4,@5,send_time,@6,status,send_content) ";
        String truncateSql = "truncate table tmp_aod_industry_data";

        System.out.println(testSql);
        PreparedStatement pstmt = conn.prepareStatement(truncateSql);
        pstmt.execute();
        pstmt.close();

        PreparedStatement pstmt1 = conn.prepareStatement(testSql);
        pstmt1.execute();
        pstmt1.close();

        conn.close();

    }

}
